package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import modelo.Proveedor;

public class AdmPersistProveedor {
private static AdmPersistProveedor instancia;
	
	private AdmPersistProveedor()
	{
	}
	public static AdmPersistProveedor getInstancia()
	{
		if (instancia == null)
			instancia = new AdmPersistProveedor();
		return instancia;
	}
	
	public Proveedor selectProveedor(String cuit)
	{
		Proveedor prov = null;
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try
		{
			String senten = "select cuit, nombre from parcialMieM.dbo.Proveedores where cuit = ?" ;
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1,cuit);
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				//if (result.getString("cuit").equals(cuit))
					prov = new Proveedor(result.getString("cuit"), result.getString("nombre"));
				
			}
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
			return prov;
		}
	      catch(Exception e) 
	      {
				System.out.println("Error al buscar proveedor");
	      }
	      return prov;
}
	public void insertProveedor(String cuit, String nombre){
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try
		{
			String senten = "insert into parcialMieM.dbo.Proveedores values (?,?)";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ps.setString(2, nombre);
			ps.execute();
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		catch(Exception e) 
	      {
				System.out.println("Error al insertar proveedor");
	      }
	}
	
	public String listarProductos(String cuit){
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try
		{
			String prod=null;
			String senten = "select descripcion from parcialMieM.dbo.Productos where cuitProv = ?";
			PreparedStatement ps = con.prepareStatement(senten);
			ps.setString(1, cuit);
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				prod+=rs.getString(1)+"\n";
			}
			PoolConnection.getPoolConnection().realeaseConnection(con);
			return prod;

		}
		catch(Exception e) 
	      {
				String prod=null;
				System.out.println("Error al listar productos proveedor");
				return prod;
	      }
	}
	
	public String listarCantidad(String cuit){
		Connection con = PoolConnection.getPoolConnection().getConnection();
		try {
			String cant=new String();
			String sent = "select cantidad=count(*) from parcialMieM.dbo.productos where cuitprov = ? group by cuitprov";
			PreparedStatement ps = con.prepareStatement(sent);
			ps.setString(1, cuit);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				cant=Integer.toString(rs.getInt("cantidad"))+"\n";
			}
			PoolConnection.getPoolConnection().realeaseConnection(con);
			return cant;
		} 
		catch (Exception e) {
			String c;
			System.out.println("Error en listar cantidad de productos");
			return c=null;
		}
	}
}
